package com.pcjavanet.collect.mes.dao.impl.jdbc.postgresql;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.core.RowCallbackHandler;

import com.pcjavanet.collect.mes.dao.AbstractDao;
import com.pcjavanet.collect.mes.dao.SpcRelOcapEmail;
import com.pcjavanet.collect.mes.dao.SpcRelOcapEmailDao;
import com.pcjavanet.collect.mes.util.CommonServerUtil;

public class SpcRelOcapEmailImpl  extends AbstractDao implements SpcRelOcapEmailDao {
	
	protected static Logger logger = Logger.getLogger( SpcRelOcapEmailImpl.class);
	
	public List<SpcRelOcapEmail> queryOcapEmailsByCreateTime(int monitorId,long beginTime, long endTime) {
		return null;
	}

	private static final String SELECT_OCAP_EMAIL_BY_KEY_SQL="select * from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email where ocap_email_key=?";
	
	public SpcRelOcapEmail getOcapEmailByKey(final int key) {
		final List<SpcRelOcapEmail> spcRelOcapEmailList  = new ArrayList<SpcRelOcapEmail>();
		jdbcTemplate.query(
			SELECT_OCAP_EMAIL_BY_KEY_SQL, 
			new PreparedStatementSetter() {
			    public void setValues(PreparedStatement ps)	throws SQLException {
					ps.setInt(1, key);
				}
			}, new RowCallbackHandler() {
				public void processRow(ResultSet rs) throws SQLException {
					SpcRelOcapEmail spcRelOcapEmail = new SpcRelOcapEmail();
					spcRelOcapEmail.setOcapEmailKey(rs.getInt("ocap_email_key"));
					spcRelOcapEmail.setChartKey(rs.getInt("chart_key"));
					spcRelOcapEmail.setFirstEmailTime(rs.getLong("first_email_time"));
					spcRelOcapEmail.setFirstOcapTime(rs.getLong("first_ocap_time"));
					spcRelOcapEmail.setMonitorKey(rs.getInt("monitor_key"));
					spcRelOcapEmail.setChartType(rs.getString("chart_type"));
					spcRelOcapEmailList.add(spcRelOcapEmail);
				}
			});
		if ( spcRelOcapEmailList.size() > 0 )
			return spcRelOcapEmailList.get(0);
		else {
			logger.error( "Can't  getOcapEmailByKey key:"+key);
			return null ; 
		}
	}
 

	private static final String UPDATE_OCAP_EMAIL_BY_KEY_SQL=
								"update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email set first_email_time=?, first_ocap_time=? where ocap_email_key=?";
	public int updateOcapEmailByKey(final SpcRelOcapEmail spcRelOcapEmail) {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				UPDATE_OCAP_EMAIL_BY_KEY_SQL,
		        new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setLong(1, spcRelOcapEmail.getFirstEmailTime());
						ps.setLong(2, spcRelOcapEmail.getFirstOcapTime());
						ps.setInt(3, spcRelOcapEmail.getOcapEmailKey());
					}
				}
		   );
		}catch (Exception e) {
			logger.error(e);
			logger.error(spcRelOcapEmail);
		}
		return result;
	}

	private static final String DELETE_OCAP_EMAIL_BY_KEY_SQL="delete from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email where ocap_email_key=?";
	public int deleteOcapEmailByKey(final int key) {
		int result = 0;
		try {
			result = jdbcTemplate.update(
				DELETE_OCAP_EMAIL_BY_KEY_SQL,
				new PreparedStatementSetter() {
					public void setValues(PreparedStatement ps)	throws SQLException {
						ps.setInt(1, key);
					}
				}
			);
		} catch (Exception e) {
			logger.error(e);
		}
		return result;
	}

	private static final String UPDATE_OCAP_EMAIL_By_CHART_KEY_SQL=
			"update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email set first_email_time=?, first_ocap_time=? where chart_key=?";
	public int updateOcapEmailByChartKey(final SpcRelOcapEmail spcRelOcapEmail) {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					UPDATE_OCAP_EMAIL_By_CHART_KEY_SQL,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
	            		ps.setLong(1, spcRelOcapEmail.getFirstEmailTime());
						ps.setLong(2, spcRelOcapEmail.getFirstOcapTime());
						ps.setInt(3, spcRelOcapEmail.getChartKey());
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
			logger.error(spcRelOcapEmail);
		}
		return result;
	}

	
	public SpcRelOcapEmail getEmptyOcapEmailByKey(int key) {
		return null;
	}

	private static final String ADD_OCAP_EMAIL_SQL="insert into "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email (chart_key,first_email_time,first_ocap_time,monitor_key,chart_type,data_key) values(?,?,?,?,?,?)";
	public int addOcapEmail(final SpcRelOcapEmail bean) {
		try {
			 jdbcTemplate.update(
					 ADD_OCAP_EMAIL_SQL,
			         new PreparedStatementSetter(){
			            public void setValues(PreparedStatement ps)	throws SQLException {
			            		ps.setInt(1, bean.getChartKey());
								ps.setLong(2,bean.getFirstEmailTime());
								ps.setLong(3, bean.getFirstOcapTime());
								ps.setInt(4, bean.getMonitorKey());
								ps.setString(5, bean.getChartType());
								ps.setInt(6, bean.getDataKey());
						}
					}
			    );
			}catch (Exception e) {
				logger.error(e);
			}
		return bean.getDataKey();
	}
	
	private static final String SELECT_MAX_OCAP_EMAIL_KEY_SQL="select max(ocap_email_key) from "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email";
	
	public int getMaxOcapEmailKey() throws DataAccessException {
		final List<Integer> ls = new ArrayList<Integer>();
		try {
			jdbcTemplate.query(
					SELECT_MAX_OCAP_EMAIL_KEY_SQL,
				new RowCallbackHandler() {
					public void processRow(ResultSet rs) throws SQLException {
						ls.add( rs.getInt(1));
					}
				}
			);
		} catch (Exception e) {
			logger.error( e );
		}
		if ( ls.size() > 0 )
			return ls.get(0);
		else {
			logger.error(" Can't getMaxOcapEmailKey "+SELECT_MAX_OCAP_EMAIL_KEY_SQL);
			return 0 ; 
		}
	}


	private static final String UPDATE_OCAP_TIME_BY_CHART_KEY="update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email set first_ocap_time=? where chart_key=?";
	public void updateOcapTimeByChartKey(final long firstOcapTime,final int chartKey) {
		int result = 0;
		try {
			result =jdbcTemplate.update(
					UPDATE_OCAP_TIME_BY_CHART_KEY,
		         new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            		ps.setLong(1, firstOcapTime);
							ps.setInt(2, chartKey);
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
		}
		//return result;
	}


	private static final String UPDATE_OCAP_TIME_BY_DATA_KEY="update "+CommonServerUtil.getSpcSchemaPrefix()+"spc_rel_ocap_email set first_ocap_time=? ,chart_key= ? where data_key=? AND chart_type=? AND first_ocap_time=0  ";
	public void updateOcapTimeByDataKey(final long firstOcapTime, final int dataKey,final  int chartKey, final String chartType ) {
		int result = 0;
		try {
			result =jdbcTemplate.update(
				UPDATE_OCAP_TIME_BY_DATA_KEY,
		        new PreparedStatementSetter(){
		            public void setValues(PreparedStatement ps)	throws SQLException {
		            		ps.setLong(1, firstOcapTime);
							ps.setInt(2, chartKey);
							ps.setInt(3,dataKey);
							ps.setString(4, chartType);
					}
				}
		    );
		}catch (Exception e) {
			logger.error(e);
		}
	}

}
